/**
 * 
 */
package br.embedded.socialapp.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

import br.embedded.socialapp.domain.Usuario;
import br.embedded.socialapp.location.Coordenates;

/**
 * @author Daniel
 *
 */
public class LocationRegisterDAO {

	private static LocationRegisterDAO singleton;
	private static final String insertQuery = "insert into location_info (data, user_login, latitude, longitude) values (?, ?, ?, ?);";
	
	private LocationRegisterDAO()
	{
		ConfigurationReader.readAllParameters();
	}
	
	public static LocationRegisterDAO getInstance()
	{
		if (singleton == null)
			singleton = new LocationRegisterDAO();
		return singleton;
	}
	
	public synchronized void registerLocation(String login, double latitude, double longitude)
	{
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			
			statement = connection.prepareStatement(insertQuery);
			statement.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			statement.setString(2, login);
			statement.setDouble(3, latitude);
			statement.setDouble(4, longitude);
			statement.execute();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
	}
	
	public synchronized List<Coordenates> getCoordenates(List<Usuario> friendList, Date initialDate, Date endDate)
	{
		Connection connection = null;
		Statement statement = null;
		
		List<Coordenates> listCoordenates = new ArrayList<Coordenates>();
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			
			String filter = listToString(friendList);
			
			String query = "select data, user_login, latitude, longitude from location_info where data >= '" + dateToString(initialDate); 
			query += "' and data <= '" + dateToString(endDate) + "' and user_login in (" + filter + ") order by data desc;";
	
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			while (resultSet.next())
			{
				Coordenates coordenates = new Coordenates();
				coordenates.setDate(resultSet.getTimestamp("data").getTime());
				coordenates.setUserLogin(resultSet.getString("user_login"));
				coordenates.setLatitude(resultSet.getDouble("latitude"));
				coordenates.setLongitude(resultSet.getDouble("longitude"));
				listCoordenates.add(coordenates);
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
		return listCoordenates;
	}
	
	private String dateToString(Date date)
	{
		SimpleDateFormat myFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
		myFormat.setTimeZone(TimeZone.getTimeZone ("GMT-3"));
		return myFormat.format(date);
	}
	
	private String listToString(List<Usuario> list)
	{
		if (list != null && list.size() > 0)
		{
			StringBuffer sb = new StringBuffer();
		
			for (int i = 0; i < list.size(); i++) {
				sb.append("'");
				sb.append(list.get(i).getLogin());
				sb.append("',");
			}
			return sb.deleteCharAt(sb.length() -1).toString();
		}
		return "";
	}
}
